package com.wlyy.bcwlw.common.export2excel.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.Converter;
import org.apache.commons.beanutils.converters.StringConverter;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.Region;

import com.wlyy.bcwlw.common.export2excel.entity.Export2ExcelColumn;
import com.wlyy.bcwlw.common.export2excel.entity.Export2ExcelHeader;
import com.wlyy.bcwlw.common.export2excel.entity.Export2ExcelRow;
import com.wlyy.bcwlw.common.export2excel.exception.ImpExpException;
import com.wlyy.bcwlw.common.export2excel.inter.IExcelExport;
import com.wlyy.bcwlw.common.utils.ExcelUtil;

public class ExcelExportImpl implements IExcelExport {
	private static final int MAX_ROW_NUM = 60000;

	private static final int DEFAULT_COlUMN_WIDTH = 20;

	private static final int MAX_COlUMN_WIDTH = 70;

	private static final int COLUMN_WIDTH_UNIT = 256;

	public HSSFWorkbook export(List<String> headers,
			List<Export2ExcelRow> data, String title) throws ImpExpException {
		List<Export2ExcelHeader> excelHeader = new ArrayList<Export2ExcelHeader>();
		if (headers != null) {
			for (String header : headers) {
				excelHeader.add(new Export2ExcelHeader(header));
			}
		}
		return export(excelHeader.toArray(new Export2ExcelHeader[0]), data,
				title);
	}

	public HSSFWorkbook export(Export2ExcelHeader[] headers,
			List<Export2ExcelRow> data, String title) throws ImpExpException {
		if (headers == null || headers.length == 0)
			throw new ImpExpException("请指定导出文件标题");
		if (data == null || data.size() == 0)
			throw new ImpExpException("导出数据为空");
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = null;
		// cell style
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 12);
		cellStyle.setFont(font);

		List<CellRangeAddress> rangs = new ArrayList<CellRangeAddress>();
		Iterator<Export2ExcelRow> it = data.iterator();
		int sheetIndex = 0;
		int i = 0;
		boolean hasTitle = false;
		while (it.hasNext()) {
			int row = i % MAX_ROW_NUM;
			if (row == 0) {
				sheet = wb.createSheet();
				wb.setSheetName(sheetIndex, (i + 1) + "-" + (i + MAX_ROW_NUM));
				sheetIndex++;
				sheet.setDefaultColumnWidth(DEFAULT_COlUMN_WIDTH);
				autosizeColumnByMaxlengthContent(sheet, headers, data);
				if (StringUtils.isNotBlank(title)) {
					hasTitle = true;
					setTitleRow(wb, sheet, headers, title, 0);
					setHeaderRow(wb, sheet, headers, 1);
				} else {
					setHeaderRow(wb, sheet, headers, 0);
				}

			}
			if (hasTitle) {
				rangs.addAll(setRow(wb, sheet, it.next(), row + 2, cellStyle));
			} else {
				rangs.addAll(setRow(wb, sheet, it.next(), row + 1, cellStyle));
			}

			i++;
		}
		// 处理合并单元格
		for (CellRangeAddress cellRangeAddress : rangs) {
			sheet.addMergedRegion(cellRangeAddress);
		}
		return wb;
	}

	private void autosizeColumnByMaxlengthContent(HSSFSheet sheet,
			Export2ExcelHeader[] headers, List<Export2ExcelRow> data) {
		Map<Integer, Integer> columnMaxWidth = new HashMap<Integer, Integer>();
		if (data.isEmpty()) {
			return;
		}
		// 如果手工设置了宽度则忽略计算
		List<Integer> ignoreAutosizeColumns = new ArrayList<Integer>();
		for (int i = 0; i < headers.length; i++) {
			if (headers[i].getWidth() != null && headers[i].getWidth() >= 0) {
				ignoreAutosizeColumns.add(i);
			}
		}
		int columnCount = data.get(0).getColumns().length;
		if (ignoreAutosizeColumns.size() == columnCount) {
			// 都手工设置了宽度，则不再计算
			return;
		}
		// 初始化宽度
		for (int i = 0; i < columnCount; i++) {
			columnMaxWidth.put(i, 0);
		}
		Export2ExcelRow row;
		Export2ExcelColumn[] columns;
		for (int i = 0; i < data.size(); i++) {
			row = data.get(i);
			columns = row.getColumns();
			for (int m = 0, length = columns.length; m < length; m++) {
				Object v = columns[m].getValue();
				if (ignoreAutosizeColumns.contains(m) || v == null
						|| StringUtils.isEmpty(String.valueOf(v))) {
					continue;
				}
				columnMaxWidth.put(m,
						Math.max(MapUtils.getIntValue(columnMaxWidth, m),
								String.valueOf(v).getBytes().length));
			}
		}

		for (Map.Entry<Integer, Integer> entry : columnMaxWidth.entrySet()) {
			if (!ignoreAutosizeColumns.contains(entry.getKey())) {
				// 乘以2是解决纯数字列宽度不足会显示科学计数法问题，乘以256才是excel真实列宽
				sheet.setColumnWidth(
						entry.getKey(),
						Math.max(
								DEFAULT_COlUMN_WIDTH * COLUMN_WIDTH_UNIT,
								(Math.min(MAX_COlUMN_WIDTH, entry.getValue()) * COLUMN_WIDTH_UNIT)));
			}
		}
	}

	private void setTitleRow(HSSFWorkbook wb, HSSFSheet sheet,
			Export2ExcelHeader[] headers, String title, int row) {
		int col = 0;
		for (Export2ExcelHeader excelHeader : headers) {
			ExcelUtil.getCell(sheet, row, col++);
		}
		sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0,
				(short) (headers.length - 1)));
		HSSFRow row2 = sheet.getRow(0);
		HSSFCell cell = row2.getCell(0);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = wb.createFont();
		font.setFontHeight((short) 350);
		font.setBoldweight((short) 2000);
		style.setFont(font);
		cell.setCellStyle(style);
		ExcelUtil.setText(cell, title);
	}

	public static void setHeaderRow(HSSFWorkbook wb, HSSFSheet sheet,
			Export2ExcelHeader[] headers, int row) {
		int col = 0;
		HSSFCell cell = null;
		for (Export2ExcelHeader header : headers) {
			cell = ExcelUtil.getCell(sheet, row, col++);
			String text = "";
			Converter converter = new StringConverter();
			text = (String) converter.convert(String.class, header.getTitle());
			ExcelUtil.setText(cell, text);
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setWrapText(true);
			HSSFFont font = wb.createFont();
			font.setBoldweight((short) 2000);
			font.setFontHeightInPoints((short) 12);
			style.setFont(font);
			cell.setCellStyle(style);

			if (header.getWidth() != null) {
				// 手工设置宽度
				sheet.setColumnWidth(col - 1, header.getWidth()
						* COLUMN_WIDTH_UNIT);
			}
		}
	}

	public static List<CellRangeAddress> setRow(HSSFWorkbook wb,
			HSSFSheet sheet, Export2ExcelRow data, int row,
			HSSFCellStyle cellStyle) {

		Export2ExcelColumn[] columns = data.getColumns();
		int col = 0;
		HSSFCell cell = null;
		Converter stringConverter = new StringConverter();
		List<CellRangeAddress> ranges = new ArrayList<CellRangeAddress>();
		for (Export2ExcelColumn export2ExceColumn : columns) {
			cell = ExcelUtil.getCell(sheet, row, col++);
			cell.setCellStyle(cellStyle);

			if (export2ExceColumn.getRowspan() != null
					&& export2ExceColumn.getRowspan() > 1) {
				ranges.add(new CellRangeAddress(cell.getRowIndex(), cell
						.getRowIndex() + export2ExceColumn.getRowspan() - 1,
						cell.getColumnIndex(), cell.getColumnIndex()));
			}

			Object item = export2ExceColumn.getValue();
			if (export2ExceColumn.getCellValueSetter() == null) {
				ExcelUtil.setText(cell,(String) stringConverter.convert(String.class, item));
			} else {
				export2ExceColumn.getCellValueSetter().setCellValue(wb, sheet,
						cell, export2ExceColumn.getValue());
			}
		}
		return ranges;
	}

	public Object export(List header, List data) throws ImpExpException {
		return this.export(header, data, "");
	}
}
